import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * 数据库连接测试工具
 * 使用方法：
 * 1. 修改下面的数据库连接参数
 * 2. 编译运行：javac database-connection-test.java && java database-connection-test
 */
public class DatabaseConnectionTest {
    
    // 请修改为您的数据库连接参数
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/jeecg-boot?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "your_password_here";
    private static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    
    public static void main(String[] args) {
        System.out.println("=== 数据库连接测试工具 ===");
        System.out.println("JDBC URL: " + JDBC_URL);
        System.out.println("Username: " + USERNAME);
        System.out.println("Driver: " + DRIVER_CLASS);
        System.out.println();
        
        Connection connection = null;
        try {
            // 1. 加载驱动
            System.out.println("1. 加载数据库驱动...");
            Class.forName(DRIVER_CLASS);
            System.out.println("✓ 驱动加载成功");
            
            // 2. 建立连接
            System.out.println("2. 建立数据库连接...");
            Properties props = new Properties();
            props.setProperty("user", USERNAME);
            props.setProperty("password", PASSWORD);
            props.setProperty("useSSL", "false");
            props.setProperty("serverTimezone", "Asia/Shanghai");
            props.setProperty("characterEncoding", "utf8");
            
            connection = DriverManager.getConnection(JDBC_URL, props);
            System.out.println("✓ 数据库连接成功");
            
            // 3. 测试查询
            System.out.println("3. 测试数据库查询...");
            try (Statement statement = connection.createStatement();
                 ResultSet rs = statement.executeQuery("SELECT VERSION() as version")) {
                
                if (rs.next()) {
                    System.out.println("✓ 数据库版本: " + rs.getString("version"));
                }
            }
            
            // 4. 检查专利表
            System.out.println("4. 检查专利表...");
            try (Statement statement = connection.createStatement();
                 ResultSet rs = statement.executeQuery("SHOW TABLES LIKE 't_patent'")) {
                
                if (rs.next()) {
                    System.out.println("✓ 专利表存在");
                    
                    // 获取专利表记录数
                    try (Statement countStatement = connection.createStatement();
                         ResultSet countRs = countStatement.executeQuery("SELECT COUNT(*) as count FROM t_patent")) {
                        
                        if (countRs.next()) {
                            System.out.println("✓ 专利表记录数: " + countRs.getInt("count"));
                        }
                    }
                } else {
                    System.out.println("✗ 专利表不存在");
                }
            }
            
            // 5. 检查时间脉络表
            System.out.println("5. 检查时间脉络表...");
            try (Statement statement = connection.createStatement();
                 ResultSet rs = statement.executeQuery("SHOW TABLES LIKE 't_patent_timeline'")) {
                
                if (rs.next()) {
                    System.out.println("✓ 时间脉络表存在");
                } else {
                    System.out.println("✗ 时间脉络表不存在，需要创建");
                }
            }
            
            // 6. 创建时间脉络表
            System.out.println("6. 创建时间脉络表...");
            String createTimelineTable = """
                CREATE TABLE IF NOT EXISTS `t_patent_timeline` (
                  `id` varchar(36) NOT NULL COMMENT '主键ID',
                  `create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
                  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
                  `update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
                  `update_time` datetime DEFAULT NULL COMMENT '更新日期',
                  `sys_org_code` varchar(64) DEFAULT NULL COMMENT '所属部门',
                  `patent_id` varchar(36) NOT NULL COMMENT '专利ID',
                  `event_type` varchar(20) NOT NULL COMMENT '事件类型：APPLY-申请，PUBLISH-公布，GRANT-授权，REJECT-驳回',
                  `event_date` date NOT NULL COMMENT '事件日期',
                  `event_title` varchar(256) NOT NULL COMMENT '事件标题',
                  `event_description` text COMMENT '事件描述',
                  `event_status` varchar(20) DEFAULT 'ACTIVE' COMMENT '事件状态：ACTIVE-有效，INACTIVE-无效',
                  `sort_order` int DEFAULT 0 COMMENT '排序顺序',
                  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
                  PRIMARY KEY (`id`),
                  KEY `idx_patent_id` (`patent_id`),
                  KEY `idx_event_date` (`event_date`),
                  KEY `idx_event_type` (`event_type`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='专利时间脉络表'
                """;
            
            try (Statement statement = connection.createStatement()) {
                statement.execute(createTimelineTable);
                System.out.println("✓ 时间脉络表创建成功");
            }
            
            // 7. 生成时间脉络数据
            System.out.println("7. 生成时间脉络数据...");
            String generateData = """
                INSERT INTO t_patent_timeline (id, patent_id, event_type, event_date, event_title, event_description, event_status, sort_order, create_time)
                SELECT 
                    UUID() as id,
                    id as patent_id,
                    'APPLY' as event_type,
                    apply_date as event_date,
                    '专利申请' as event_title,
                    CONCAT('专利《', COALESCE(zh_name, origin_name), '》提交申请') as event_description,
                    'ACTIVE' as event_status,
                    1 as sort_order,
                    NOW() as create_time
                FROM t_patent 
                WHERE apply_date IS NOT NULL
                
                UNION ALL
                
                SELECT 
                    UUID() as id,
                    id as patent_id,
                    'PUBLISH' as event_type,
                    apply_public_date as event_date,
                    '专利申请公布' as event_title,
                    CONCAT('专利《', COALESCE(zh_name, origin_name), '》申请公布') as event_description,
                    'ACTIVE' as event_status,
                    2 as sort_order,
                    NOW() as create_time
                FROM t_patent 
                WHERE apply_public_date IS NOT NULL
                """;
            
            try (Statement statement = connection.createStatement()) {
                int rows = statement.executeUpdate(generateData);
                System.out.println("✓ 时间脉络数据生成成功，共 " + rows + " 条记录");
            }
            
            System.out.println();
            System.out.println("=== 测试完成 ===");
            System.out.println("✓ 数据库连接正常");
            System.out.println("✓ 时间脉络功能已准备就绪");
            
        } catch (Exception e) {
            System.out.println("✗ 测试失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("✓ 数据库连接已关闭");
                } catch (Exception e) {
                    System.out.println("✗ 关闭连接失败: " + e.getMessage());
                }
            }
        }
    }
}
